import os
import pymysql
import pandas as pd
from sqlalchemy import create_engine

def get_tables(db_file = 'main.db'):

    try:

        conn = sqlite3.connect(db_file)

        cur = conn.cursor()

        cur.execute("select name from sqlite_master where type='table' order by name")

        return cur.fetchall()

    except sqlite3.Error as e:

            print(e)


def trans_future_index_data_1m_mysql():
    data_name = 'future_index_data_1m'
    path = './' + data_name + '/'
    db = create_engine(f'mysql+pymysql://yhlz:86888196@localhost/{data_name}?charset=utf8')
    for file in os.listdir(path):
        print(f'dispose {file}')
        temp = pd.read_csv(path + file)
        temp.to_sql(file.replace('.csv', '').lower(), db, if_exists='replace')

if __name__ == '__main__':
    while True:
        func = ['1:将future_index_data_1m中期货数据导入到mysql中','q: 退出']
        for item in func:
            print(item)

        input_ = input('请选择！')

        if input_ == '1':
            trans_future_index_data_1m_mysql()
        elif input_ == 'q':
            break
        else:
            print('unknown option, input again!')
